package com.wc.wsclub.service;

import com.wc.wsclub.entity.Wsn;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Objects;
@Service
public  class WsServiceimpt implements WsService {

@Autowired
    private JdbcTemplate jdbcTemplate;

@Override
public Wsn login(String username, String password){
    String sql="select * from user where username =? and password =?";
    return jdbcTemplate.queryForObject(sql,new Object[]{username,password},new BeanPropertyRowMapper<>(Wsn.class));
}


@Override
    public String getAllusers(){
    return jdbcTemplate.queryForObject("select count(1) from wsclub",String.class);
}
@Override
    public List<Map<String, Object>>findAll() {
        String sql="select * from wsclub";
        List<Map<String,Object>> List=jdbcTemplate.queryForList(sql);
        return List;
    }

    @Override
    public Wsn getByid(int id){
    String sql="select * from wsnclub where vipnumber=?";
    List<Wsn>stu =jdbcTemplate.query(sql,new Object[]{id},new WsnRowMapper());
    Wsn wsn =null;
    if(!stu.isEmpty()){
        System.out.println("获取失败");
    }
    return wsn;
    }
    /**
     * 插入用户-防止sql注入-可以返回该条记录的主键
     * @param wsn
     * @return
     */
@Override
    public  int addWsn(Wsn wsn){
    String sql="insert into wsclub(username,vipenrolldate,email,vipoverduedate,vipnumber,role,wieght,height) values(?,?,?,?,?,?,?,?)";
    KeyHolder keyHolder =new GeneratedKeyHolder();
    int resRow=jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps=con.prepareStatement(sql,new String[]{"id"});
            ps.setString(1,wsn.getUsername());
            ps.setDate(2, (Date) wsn.getVipenrolldate());
            ps.setString(3,wsn.getEmail());
            ps.setDate(4, (Date) wsn.getVipverduedate());
            ps.setInt(5,wsn.getVipnumber());
            ps.setString(6,wsn.getRole());
            ps.setDouble(7,wsn.getWeight());
            ps.setDouble(8,wsn.getHeight());
            return ps;
        }
    },keyHolder);
    System.out.println("操作记录数："+resRow+"主键："+keyHolder.getKey());
    return Integer.parseInt(keyHolder.getKey().toString());
}

@Override
public  int updateWsn(Wsn wsn){
    String sql="update student set username=?,email=?,role=?";
    int res=jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1,wsn.getUsername());
            ps.setString(2,wsn.getEmail());
            ps.setString(3,wsn.getRole());
        }
    });
    return res;
}


    @Override
    public int deleteWsn(int id) {
        String sql = "delete from wsclub where id=?";
        return jdbcTemplate.update(sql, id);
    }
    @Override
    public int isHasWsn(Long id) {
        String sql = "select count(*) from wsclub where id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, id);
    }



    class WsnRowMapper implements RowMapper<Wsn>{

    @Override
        public Wsn mapRow(ResultSet resultSet,int i)throws SQLException{
        Wsn wsn=new Wsn();
        wsn.setId(resultSet.getLong("id"));
        wsn.setUsername(resultSet.getString("username"));
        wsn.setHeight(resultSet.getDouble("height"));
        wsn.setVipenrolldate(resultSet.getDate("vipeenrolldate"));
        wsn.setWeight(resultSet.getDouble("weight"));
        wsn.setVipverduedate(resultSet.getDate("vipverduedate"));
        return wsn;

    }


    }

}
